package com.aaa.dao;

import com.aaa.entity.Film;
import com.aaa.entity.Ftype;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class FilmDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    private RowMapper<Film> rowMapper=new RowMapper<Film>() {
        public Film mapRow(ResultSet resultSet, int i) throws SQLException {
            Film film=new Film();
            film.setId(resultSet.getLong("id"));
            film.setChinese_name(resultSet.getString("chinese_name"));
            film.setEnglish_name(resultSet.getString("english_name"));
            film.setImgUrl(resultSet.getString("img_url"));
            film.setDuration(resultSet.getString("duration"));
            film.setShowtimes(resultSet.getString("showtimes"));
            film.setIntroduction(resultSet.getString("introduction"));
            film.setCountry(resultSet.getString("country"));
            film.setDirector(resultSet.getString("director"));
            film.setDmg(resultSet.getString("dmg"));
            film.setActors(resultSet.getString("actors"));
            film.setAmg(resultSet.getString("amg"));
            Ftype ftype=new Ftype();
            ftype.setTid(resultSet.getLong("tid"));
            ftype.setName(resultSet.getString("name"));
            film.setFtype(ftype);
            return film;
        }
    };


    public Film checkName(Film film){
        String sql="select f.id,f.chinese_name,f.english_name,f.img_url,f.duration,f.showtimes,f.introduction,f.country,f.director,f.dmg,f.actors,f.amg,t.tid,t.name from film f inner join ftype t on f.tid=t.tid where chinese_name=? ";
        List<Film> result = jdbcTemplate.query(sql, rowMapper, film.getChinese_name(), film.getEnglish_name());
        if (result.size()>0){
            return result.get(0);
        }else {
            return null;
        }

    }


    /**
     * 查询当前页数据
     * @param
     * @param offset
     * @param limit
     * @return
     */
    public List<Film> listPage(String keyword,String country,String director,String actors , String tid, Integer offset,Integer limit){
        String sql="select f.id,f.chinese_name,f.english_name,f.img_url,f.duration,f.showtimes,f.introduction,f.country,f.director,f.dmg,f.actors,f.amg,t.tid,t.name from film f inner join ftype t on f.tid=t.tid where 1=1 ";
        if (keyword!=null&&keyword.length()>0){
            sql+=" and chinese_name like '%"+keyword+"%'";
        }
        if (country!=null&&country.length()>0){
            sql+=" and country like '%"+country+"%'";
        }
        if (director!=null&&director.length()>0){
            sql+=" and director like '%"+director+"%'";
        }
        if (actors!=null&&actors.length()>0){
            sql+=" and actors like '%"+actors+"%'";
        }
        if (tid!=null&&tid.length()>0){
            sql+=" and t.tid = "+tid+"";
        }
        sql+=" order by f.id desc ";
        sql+=" limit ?,?";
        List<Film> result = jdbcTemplate.query(sql, rowMapper, offset, limit);
        return result;
    }

    /**
     * 查询总条数
     * @param keyword
     * @return
     */
    public Integer count(String keyword){
        String sql="select count(id) as c from film where chinese_name like ?";
        final Integer result=jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },"%"+keyword+"%");
        return result;
    }




    /**
     * 保存电影信息
     * @param film
     * @return
     */
    public Integer save(Film film){
        String sql="insert into film(chinese_name,english_name,img_url,duration,showtimes,introduction,country,director,dmg,actors,amg,tid) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        int count = jdbcTemplate.update(sql, film.getChinese_name(), film.getEnglish_name(), film.getImgUrl(), film.getDuration(), film.getShowtimes(), film.getIntroduction(), film.getCountry(), film.getDirector(), film.getDmg(), film.getActors(), film.getAmg(),film.getFtype().getTid());
        return count;
    }

    /**
     * 修改电影信息
     * @param film
     * @return
     */
    public Integer update(Film film){
        String sql="update film set chinese_name=?,english_name=?,img_url=?,duration=?,showtimes=?,introduction=?,country=?,director=?,dmg=?,actors=?,amg=?,tid=? where id=?";
        int count = jdbcTemplate.update(sql, film.getChinese_name(), film.getEnglish_name(), film.getImgUrl(), film.getDuration(), film.getShowtimes(), film.getIntroduction(), film.getCountry(), film.getDirector(), film.getDmg(), film.getActors(), film.getAmg(),film.getFtype().getTid(), film.getId());
        return count;
    }

    /**
     * 删除电影信息
     * @param id
     * @return
     */
    public Integer delete(Long id){
        String sql="delete from film where id=?";
        int count = jdbcTemplate.update(sql, id);
        return count;
    }


    public Integer countByName(String name){
        String sql = "select count(id) as c from film where Chinese_name = ? ";
        Integer result = this.jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },name);
        return  result;
    }

    public Integer countByName(String name,Integer id){
        String sql = "select count(id) as c from film where Chinese_name = ? and id != ? ";
        Integer result = this.jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },name,id);
        return  result;
    }



}
